"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : GSC功能模块
Case Name   : 备机gs_gsc_clean清理所有的数据库全局系统缓存
Create At   : 2023/05/24
Owner       : @peilinqian
Description :
    1、打开gsc开关;
    2、重启数据库生效;
    3、创建数据库;
    4、创建表;
    5、备机查询表及并连接所有库;
    6、查看syscache占用情况;
    6.1、备机查看gs_gsc_memory_detail占用情况;
    6.2、备机查看gs_gsc_dbstat_info占用情况;
    6.3、备机查看gs_gsc_table_detail占用情况;
    6.4、备机查看gs_gsc_catalog_detail占用情况;
    7、清除所有的数据库全局系统缓存;
    8、查看syscache占用情况;
    8.1、备机查看gs_gsc_memory_detail占用情况;
    8.2、备机查看gs_gsc_dbstat_info占用情况;
    8.3、备机查看gs_gsc_table_detail占用情况;
    8.4、备机查看gs_gsc_catalog_detail占用情况;
Expect      :
    1、打开gsc开关; 修改成功
    2、重启数据库生效; 重启成功
    3、创建数据库; 创建成功
    4、创建表; 操作成功
    5、备机查询表及并连接所有库; 查询且连接成功
    6、查看syscache占用情况; 缓存信息正确
    6.1、备机查看gs_gsc_memory_detail占用情况; 缓存库数量为所有库数量
    6.2、备机查看gs_gsc_dbstat_info占用情况; 缓存库数量为所有库数量
    6.3、备机查看gs_gsc_table_detail占用情况; 缓存库数量为所有库数量
    6.4、备机查看gs_gsc_catalog_detail占用情况; 缓存库数量为所有库数量
    7、清除所有的数据库全局系统缓存; 清除成功
    8、查看syscache占用情况; 缓存信息正确
    8.1、备机查看gs_gsc_memory_detail占用情况; 缓存库数量减少，每个库所占usedsize减少
    8.2、备机查看gs_gsc_dbstat_info占用情况; 缓存库数量减少，每个库所占total_memory减少
    8.3、备机查看gs_gsc_table_detail占用情况; 缓存库数量减少，每个库缓存数量减少
    8.4、备机查看gs_gsc_catalog_detail占用情况; 缓存库数量减少，每个库缓存数量减少
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info("----this is setup----")
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.db_name = 'db_gsc0052'
        self.tb_name = 'tb_gsc0052'
        self.com = Common()
        self.constant = Constant()
        self.sta1_sh = CommonSH('Standby1DbUser')
        self.pri_dbuser = Node(node='PrimaryDbUser')

    def test_main(self):
        step_txt = '----step0:查看数据库状态是否正常;----'
        self.log.info(step_txt)
        status_result = pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)
        step_txt = '----step0:查看enable_global_syscache初始配置值;----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param("enable_global_syscache")

        if self.init_para1 == 'off':
            step_txt = '----step1:打开gsc开关; expect:修改成功----'
            self.log.info(step_txt)
            result = pri_sh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_global_syscache= on")
            self.assertTrue(result, '执行失败:' + step_txt)

            step_txt = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(step_txt)
            restart = pri_sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + step_txt)
            self.is_reset_para = True
        else:
            step_txt = '----gsc开关设置正常，跳过step1、step2----'
            self.log.info(step_txt)
            self.is_reset_para = False

        step_txt = '----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'drop database if exists {self.db_name};' \
            f'create database {self.db_name};'
        create_result = pri_sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step4:创建表; expect:创建成功----'
        self.log.info(step_txt)
        create_tb_sql = f"create table {self.tb_name} " \
            f"(c_integer integer,c_tinyint tinyint," \
            f"c_smallint smallint,c_binary_integer binary_integer," \
            f"c_bigint bigint,c_numeric numeric(10,4)," \
            f"c_number number(10,4),c_smallserial smallserial);"
        create_result = pri_sh.execut_db_sql(create_tb_sql,
                                             dbname=f'{self.db_name}')
        self.log.info(create_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        self.log.info('--等待主备同步--')
        self.sta1_sh.check_location_consistency('standby', node_num)

        step_txt = '----step5:备机查询表及并连接所有库;  expect:查询且连接成功----'
        self.log.info(step_txt)
        self.log.info('----备机查询表----')
        select_sql = f"select * from {self.tb_name};"
        create_result = self.sta1_sh.execut_db_sql(select_sql,
                                                   dbname=f'{self.db_name}')
        self.log.info(create_result)
        self.assertIn('0 rows', create_result, '执行失败:' + step_txt)

        db_oid_sql = f"select oid from pg_database where " \
            f"datname = '{self.pri_dbuser.db_name}';"
        db_oid = self.sta1_sh.execut_db_sql(db_oid_sql).splitlines()[
            -2].strip()
        self.log.info("当前连接数据库oid" + db_oid)
        self.log.info('----备机查询所有库并进行连接----')
        select_db_sql = "select datname from pg_database where datname " \
                        "not like('template%');"
        select_db_result = self.sta1_sh.execut_db_sql(select_db_sql)
        self.log.info(select_db_result)
        db_list = select_db_result.splitlines()[2:-1:1]
        for i in db_list:
            con_result = self.sta1_sh.execut_db_sql(
                'select current_database();',
                dbname=f'{i}')
            self.log.info(con_result)
            self.assertIn(i, con_result, '执行失败:' + step_txt)

        step_txt = '----step6:备机查看syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        check_sql1 = "select db_id,usedsize from gs_gsc_memory_detail;;"
        check_sql2 = "select database_id,total_memory,tup_count,rel_count," \
                     "database_name from gs_gsc_dbstat_info();"
        check_sql3 = "select database_oid,count(*) from " \
                     "gs_gsc_table_detail() group by database_oid;"
        check_sql4 = "select database_id,count(*) from " \
                     "gs_gsc_catalog_detail() group by database_id;"

        self.log.info('缓存所有库数量为：所有库+0库')
        self.total_db_num = len(db_list) + 1

        step_txt = '----step6.1:备机查看gs_gsc_memory_detail占用情况; ' \
                   'expect:缓存库数量为所有库数量----'
        self.log.info(step_txt)
        check_result6_1 = self.check_db_size(check_sql1)
        check_db_list6_1 = check_result6_1[0]
        self.check_db_size6_1 = check_result6_1[1]
        self.assertEqual(self.total_db_num, len(check_db_list6_1),
                         '执行失败:' + step_txt)
        step_txt = '----step6.2:备机查看gs_gsc_dbstat_info占用情况; ' \
                   'expect:缓存库数量为所有库数量----'
        self.log.info(step_txt)
        check_result6_2 = self.check_db_size(check_sql2)
        check_db_list6_2 = check_result6_2[0]
        self.check_db_size6_2 = check_result6_2[1]
        self.assertEqual(self.total_db_num, len(check_db_list6_2),
                         '执行失败:' + step_txt)

        step_txt = '----step6.3:备机查看gs_gsc_table_detail占用情况; ' \
                   'expect:缓存库数量为所有库数量----'
        self.log.info(step_txt)
        check_result6_3 = self.check_db_size(check_sql3)
        check_db_list6_3 = check_result6_3[0]
        self.check_db_count6_3 = check_result6_3[1]
        self.assertEqual(self.total_db_num, len(check_db_list6_3),
                         '执行失败:' + step_txt)
        step_txt = '----step6.4:备机查看gs_gsc_catalog_detail占用情况; ' \
                   'expect:缓存库数量为所有库数量----'
        self.log.info(step_txt)
        check_result6_4 = self.check_db_size(check_sql4)
        check_db_list6_4 = check_result6_4[0]
        self.check_db_count6_4 = check_result6_4[1]
        self.assertEqual(self.total_db_num, len(check_db_list6_4),
                         '执行失败:' + step_txt)

        step_txt = '----step7:清除所有的数据库全局系统缓存; expect:清除成功----'
        self.log.info(step_txt)
        clean_sql = f"select * from gs_gsc_clean(-1);"
        clean_result = self.sta1_sh.execut_db_sql(clean_sql)
        self.log.info(clean_result)
        self.assertEqual(clean_result.splitlines()[-2].strip().lower(), 't',
                         '执行失败:' + step_txt)

        step_txt = '----step8:查看syscache占用情况; expect:缓存信息正确----'
        self.log.info(step_txt)
        step_txt = '----step8.1:备机查看gs_gsc_memory_detail占用情况; ' \
                   'expect:缓存库数量减少，每个库所占usedsize减少;' \
                   '(ps:当前连接库可能因为有占用usedsize未减少)----'
        self.log.info(step_txt)
        check_result8_1 = self.check_db_size(check_sql1)
        check_db_list8_1 = check_result8_1[0]
        self.check_db_size8_1 = check_result8_1[1]
        self.assertLess(len(check_db_list8_1), len(check_db_list6_1),
                        '执行失败:' + step_txt)
        for i in check_db_list8_1:
            if db_oid != i:
                self.assertLess(self.check_db_size8_1[i],
                                self.check_db_size6_1[i], '执行失败:' + step_txt)
        step_txt = '----step8.2:备机查看gs_gsc_dbstat_info占用情况; ' \
                   'expect:缓存库数量减少，每个库所占total_memory减少----'
        self.log.info(step_txt)
        check_result8_2 = self.check_db_size(check_sql2)
        check_db_list8_2 = check_result8_2[0]
        self.check_db_size8_2 = check_result8_2[1]
        self.assertLess(len(check_db_list8_2), len(check_db_list6_2),
                        '执行失败:' + step_txt)
        for i in check_db_list8_2:
            if db_oid != i:
                self.assertLess(self.check_db_size8_2[i],
                                self.check_db_size6_2[i], '执行失败:' + step_txt)
        step_txt = '----step8.3:备机查看gs_gsc_table_detail占用情况; ' \
                   'expect:缓存库数量减少，每个库缓存数量减少----'
        self.log.info(step_txt)
        check_result8_3 = self.check_db_size(check_sql3)
        check_db_list8_3 = check_result8_3[0]
        self.check_db_count8_3 = check_result8_3[1]
        self.assertLess(len(check_db_list8_2), len(check_db_list6_3),
                        '执行失败:' + step_txt)
        for i in check_db_list8_3:
            self.assertLess(self.check_db_count8_3[i],
                            self.check_db_count6_3[i], '执行失败:' + step_txt)

        step_txt = '----step8.4:备机查看gs_gsc_catalog_detail占用情况; ' \
                   'expect:缓存库数量减少，每个库缓存数量减少----'
        self.log.info(step_txt)
        check_result8_4 = self.check_db_size(check_sql4)
        check_db_list8_4 = check_result8_4[0]
        self.check_db_count8_4 = check_result8_4[1]
        self.assertLess(len(check_db_list8_4), len(check_db_list6_4),
                        '执行失败:' + step_txt)
        for i in check_db_list8_3:
            self.assertLess(self.check_db_count8_4[i],
                            self.check_db_count6_4[i], '执行失败:' + step_txt)

    def check_db_size(self, sql):
        """
        :param: sql: 查询对象sql
        :return: 返回数据库与对应查询结果字典
        """
        select_result = self.sta1_sh.execut_db_sql(sql)
        self.log.info(select_result)
        result = select_result.splitlines()[2:-1]
        db_list = []
        db_dict = dict()
        for i in result:
            db_dict[i.split('|')[0].strip()] = int(i.split('|')[1].strip())
            db_list.append(f"{i.split('|')[0].strip()}")
        return db_list, db_dict

    def tearDown(self):
        step_txt = '----this is teardown----'
        self.log.info(step_txt)
        step1_txt = '----清理数据库; expect:清理成功----'
        self.log.info(step1_txt)
        drop_cmd = f"drop database if exists {self.db_name};"
        drop_result = pri_sh.execut_db_sql(drop_cmd)
        self.log.info(drop_result)

        if self.is_reset_para:
            self.step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step2_txt)
            self.msg1 = pri_sh.execute_gsguc('set',
                                             self.constant.GSGUC_SUCCESS_MSG,
                                             f"enable_global_syscache "
                                             f"={self.init_para1}")

            self.step3_txt = '----重启数据库生效; expect:重启成功----'
            self.log.info(self.step3_txt)
            self.restart = pri_sh.restart_db_cluster()
            self.step4_txt = '----查询数据库状态; expect:状态正常----'
            self.log.info(self.step4_txt)
            self.status_result = pri_sh.get_db_cluster_status('status')

        self.log.info(f'----{os.path.basename(__file__)}:end----')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_result,
                      "执行失败" + step_txt + step1_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + self.step2_txt)
            self.assertTrue(self.restart, '执行失败:' + self.step3_txt)
            self.assertTrue(self.status_result, '执行失败:' + self.step4_txt)
